/// Author: Samle
import 'package:intl/intl.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

import 'db_default.dart';

class DBHelper {
  static Database? db;
  static String? path;
  final String booksTB = "booksTB";  /// <<<<<<<< booksTB
  final String wordsTB = "wordsTB";  /// <<<<<<<< wordsTB
  DBHelper._();

  /// 初始化数据库文件
  Future<Database?> initDB() async {
    if (db != null) {
      return db;
    } else {
      path = join(await getDatabasesPath(), 'localData.db');
      db = await openDatabase(
        path!,
        version: 1,
        onCreate: (db, ver) async => await populateDB(db),
      );
      return db;
    }
  }

  /// 初始化数据库
  populateDB(Database db) async {
    /// <<<<<<<< books
    await db.execute(
        "CREATE TABLE IF NOT EXISTS $booksTB(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,path TEXT,cover TEXT,num INTEGER,content TEXT,source TEXT)");
    /// 导入初始数据
    for (int i = 0; i < books.length; i++) {
      List args = [books[i].id, '${books[i].name}', '${books[i].path}', '${books[i].cover}', books[i].num, '${books[i].content}', '${books[i].source}'];
      await db.rawInsert(
      "INSERT INTO $booksTB (id, name, path, cover, num, content, source) VALUES(?, ?, ?, ?, ?, ?, ?)", args);
    }
    /// <<<<<<<< words
    await db.execute(
        "CREATE TABLE IF NOT EXISTS $wordsTB(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,trans TEXT,ukphone TEXT,ukspeech TEXT,usphone TEXT,usspeech TEXT,syno TEXT,relWord TEXT,sentence TEXT,phrase TEXT,exam TEXT,belong TEXT,checkTimes INTEGER,errorTimes INTEGER,helpText TEXT,soundText TEXT)");
  }

  /// Books 表单新增实例
  Future<int> addBooks(Map dataForm) async {
    await initDB();
    String query =
        "INSERT INTO $booksTB (id, name, path, cover, num, content, source) VALUES(?, ?, ?, ?, ?, ?, ?)";
    List args = [
      (dataForm['id']),
      (dataForm['name'] != null ? dataForm['name'].toString() : ''),
      (dataForm['path'] != null ? dataForm['path'].toString() : ''),
      (dataForm['cover'] != null ? dataForm['cover'].toString() : ''),
      (dataForm['num']),
      (dataForm['content'] != null ? dataForm['content'].toString() : ''),
      (dataForm['source'] != null ? dataForm['source'].toString() : '')
    ];
    var res = await db!.rawInsert(query, args);
    return res;
  }

  /// Books 表单删除实例
  Future<int> deleteBooks(int id) async {
    await initDB();
    String query = "DELETE FROM $booksTB WHERE id=$id";
    var res = await db!.rawDelete(query);
    return res;
  }

  /// Books 表单修改实例
  Future<int> updateBooks(Map dataForm) async {
    await initDB();
    String query =
        'UPDATE $booksTB SET  name = \'${dataForm['name'] != null ? dataForm['name'].toString() : ''}\', path = \'${dataForm['path'] != null ? dataForm['path'].toString() : ''}\', cover = \'${dataForm['cover'] != null ? dataForm['cover'].toString() : ''}\', num = \'${dataForm['num']}\', content = \'${dataForm['content'] != null ? dataForm['content'].toString() : ''}\', source = \'${dataForm['source'] != null ? dataForm['source'].toString() : ''}\' WHERE id = ${dataForm['id']}';
    var res = await db!.rawUpdate(query);
    return res;
  }

  /// Books 表单查询实例
  Future<List<Map>> getBooks() async {
    await initDB();
    String query = "SELECT * FROM $booksTB";
    /// 需要排序按需添加
    /// query = query + " ORDER BY id DESC , updateTime ASC";
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    return List.generate(
      res.length,
          (i) => {
        "id": res[i]['id'],
        "name": res[i]['name'],
        "path": res[i]['path'],
        "cover": res[i]['cover'],
        "num": res[i]['num'],
        "content": res[i]['content'],
        "source": res[i]['source'],
      },
    );
  }

  /// Books 表单查询单一实例
  Future<Map> getBooksById(int id) async {
    await initDB();
    String query = "SELECT * FROM $booksTB WHERE id='$id'";
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    if (res.isNotEmpty) {
      return {
        "id": res[0]['id'],
        "name": res[0]['name'],
        "path": res[0]['path'],
        "cover": res[0]['cover'],
        "num": res[0]['num'],
        "content": res[0]['content'],
        "source": res[0]['source'],
      };
    } else {
      return {};
    }
  }

  /// Books 表单查询搜索实例
  Future<List<Map>> getBooksBySearch(Map paramsForm) async {
    await initDB();
    String query = "SELECT * FROM $booksTB";
    /// 设置统一搜索
    String searchStr = "";
    if (paramsForm['search'] != null && paramsForm['search'] != '') {
      searchStr = "( name LIKE '%${paramsForm['search']}%' or content LIKE '%${paramsForm['search']}%' or source LIKE '%${paramsForm['search']}%' )";
    }
    /// 参数搜索类型
    Map paramsMap = {
      'id': 'equal',
      'name': 'equal',
      'path': 'none',
      'cover': 'none',
      'num': 'none',
      'content': 'like',
      'source': 'equal',
    };
    String queryParams = "";
    paramsForm.forEach((key, value) {
      if (value != null && value != "") {
        String params = "";
        /// 时间参数需要自行按照如下样式进行调整
        if (key == "endTimeStart") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 00:00:00";
          params = " datetime(endTime) >= datetime('$newValue')  ";
        } else if (key == "endTimeEnd") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 23:59:59";
          params = " datetime(endTime) <= datetime('$newValue')  ";
        } else if (key == "startTimeStart") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 00:00:00";
          params = " datetime(startTime) >= datetime('$newValue')  ";
        } else if (key == "startTimeEnd") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 23:59:59";
          params = " datetime(startTime) <= datetime('$newValue')  ";
        } else if (key != "id") {
          if ( paramsMap.containsKey(key) && paramsMap[key] == 'equal'){
            params = " $key = '$value' ";
          } else if ( paramsMap.containsKey(key) && paramsMap[key] == 'like' ) {
            params = " $key LIKE '%$value%' ";
          }
        } else if (key == "id") {
          params = " $key = '$value' ";
        }
        if ( params != "" ) {
          if ( queryParams != "" ) {
            queryParams = queryParams + " and " + params;
          } else {
            queryParams = queryParams + params;
          }
        }
      }
    });
    queryParams = (searchStr != "" ? (queryParams != "" ? searchStr + " and ": searchStr):"") + queryParams;
    if ( queryParams != "" ) { query = query + " WHERE " + queryParams; }
    /// 需要排序按需添加
    /// query = query + " ORDER BY id DESC , updateTime ASC";
    /// 按参数判断是否分页
    if (paramsForm.containsKey('pageSize') && paramsForm['pageSize'] != null && paramsForm['pageSize'] != '' && paramsForm['pageSize'] != 'null' && paramsForm.containsKey('pageNum') && paramsForm['pageNum'] != null && paramsForm['pageNum'] != '' && paramsForm['pageNum'] != 'null') {
      var pageSize = int.parse(paramsForm['pageSize'].toString());
      var pageNum = int.parse(paramsForm['pageNum'].toString());
      query = query + "  Limit $pageSize Offset ${pageSize * (pageNum - 1)}";
    }
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    return List.generate(
      res.length,
          (i) => {
        "id": res[i]['id'],
        "name": res[i]['name'],
        "path": res[i]['path'],
        "cover": res[i]['cover'],
        "num": res[i]['num'],
        "content": res[i]['content'],
        "source": res[i]['source'],
      },
    );
  }

  /// Books 表单按条件查询实例
  /// 在 getBooksBySearch(Map paramsForm) 中集成了所有的搜索参数，如无必要不需要单独使用此搜索方法
  Future<List<Map>> getBooksByName(String name) async {
    await initDB();
    String query = "SELECT * FROM $booksTB WHERE name = '$name'";
    /// 需要排序按需添加
    /// query = query + " ORDER BY id DESC , updateTime ASC";
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    return List.generate(
      res.length,
          (i) => {
        "id": res[i]['id'],
        "name": res[i]['name'],
        "path": res[i]['path'],
        "cover": res[i]['cover'],
        "num": res[i]['num'],
        "content": res[i]['content'],
        "source": res[i]['source'],
      },
    );
  }

  /// Words 表单新增实例
  Future<int> addWords(Map dataForm) async {
    await initDB();
    String query =
        "INSERT INTO $wordsTB (id, name, trans, ukphone, ukspeech, usphone, usspeech, syno, relWord, sentence, phrase, exam, belong, checkTimes, errorTimes, helpText, soundText) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    List args = [
      (dataForm['id']),
      (dataForm['name'] != null ? dataForm['name'].toString() : ''),
      (dataForm['trans'] != null ? dataForm['trans'].toString() : ''),
      (dataForm['ukphone'] != null ? dataForm['ukphone'].toString() : ''),
      (dataForm['ukspeech'] != null ? dataForm['ukspeech'].toString() : ''),
      (dataForm['usphone'] != null ? dataForm['usphone'].toString() : ''),
      (dataForm['usspeech'] != null ? dataForm['usspeech'].toString() : ''),
      (dataForm['syno'] != null ? dataForm['syno'].toString() : ''),
      (dataForm['relWord'] != null ? dataForm['relWord'].toString() : ''),
      (dataForm['sentence'] != null ? dataForm['sentence'].toString() : ''),
      (dataForm['phrase'] != null ? dataForm['phrase'].toString() : ''),
      (dataForm['exam'] != null ? dataForm['exam'].toString() : ''),
      (dataForm['belong'] != null ? dataForm['belong'].toString() : ''),
      (dataForm['checkTimes']),
      (dataForm['errorTimes']),
      (dataForm['helpText'] != null ? dataForm['helpText'].toString() : ''),
      (dataForm['soundText'] != null ? dataForm['soundText'].toString() : '')
    ];
    var res = await db!.rawInsert(query, args);
    return res;
  }

  /// Words 表单删除实例
  Future<int> deleteWords(int id) async {
    await initDB();
    String query = "DELETE FROM $wordsTB WHERE id=$id";
    var res = await db!.rawDelete(query);
    return res;
  }

  /// Words 表单修改实例
  Future<int> updateWords(Map dataForm) async {
    await initDB();
    String query =
        'UPDATE $wordsTB SET  name = \'${dataForm['name'] != null ? dataForm['name'].toString() : ''}\', trans = \'${dataForm['trans'] != null ? dataForm['trans'].toString() : ''}\', ukphone = \'${dataForm['ukphone'] != null ? dataForm['ukphone'].toString() : ''}\', ukspeech = \'${dataForm['ukspeech'] != null ? dataForm['ukspeech'].toString() : ''}\', usphone = \'${dataForm['usphone'] != null ? dataForm['usphone'].toString() : ''}\', usspeech = \'${dataForm['usspeech'] != null ? dataForm['usspeech'].toString() : ''}\', syno = \'${dataForm['syno'] != null ? dataForm['syno'].toString() : ''}\', relWord = \'${dataForm['relWord'] != null ? dataForm['relWord'].toString() : ''}\', sentence = \'${dataForm['sentence'] != null ? dataForm['sentence'].toString() : ''}\', phrase = \'${dataForm['phrase'] != null ? dataForm['phrase'].toString() : ''}\', exam = \'${dataForm['exam'] != null ? dataForm['exam'].toString() : ''}\', belong = \'${dataForm['belong'] != null ? dataForm['belong'].toString() : ''}\', checkTimes = \'${dataForm['checkTimes']}\', errorTimes = \'${dataForm['errorTimes']}\', helpText = \'${dataForm['helpText'] != null ? dataForm['helpText'].toString() : ''}\', soundText = \'${dataForm['soundText'] != null ? dataForm['soundText'].toString() : ''}\' WHERE id = ${dataForm['id']}';
    var res = await db!.rawUpdate(query);
    return res;
  }

  /// Words 表单查询实例
  Future<List<Map>> getWords() async {
    await initDB();
    String query = "SELECT * FROM $wordsTB";
    /// 需要排序按需添加
    /// query = query + " ORDER BY id DESC , updateTime ASC";
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    return List.generate(
      res.length,
          (i) => {
        "id": res[i]['id'],
        "name": res[i]['name'],
        "trans": res[i]['trans'],
        "ukphone": res[i]['ukphone'],
        "ukspeech": res[i]['ukspeech'],
        "usphone": res[i]['usphone'],
        "usspeech": res[i]['usspeech'],
        "syno": res[i]['syno'],
        "relWord": res[i]['relWord'],
        "sentence": res[i]['sentence'],
        "phrase": res[i]['phrase'],
        "exam": res[i]['exam'],
        "belong": res[i]['belong'],
        "checkTimes": res[i]['checkTimes'],
        "errorTimes": res[i]['errorTimes'],
        "helpText": res[i]['helpText'],
        "soundText": res[i]['soundText'],
      },
    );
  }

  /// Words 表单查询单一实例
  Future<Map> getWordsById(int id) async {
    await initDB();
    String query = "SELECT * FROM $wordsTB WHERE id='$id'";
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    if (res.isNotEmpty) {
      return {
        "id": res[0]['id'],
        "name": res[0]['name'],
        "trans": res[0]['trans'],
        "ukphone": res[0]['ukphone'],
        "ukspeech": res[0]['ukspeech'],
        "usphone": res[0]['usphone'],
        "usspeech": res[0]['usspeech'],
        "syno": res[0]['syno'],
        "relWord": res[0]['relWord'],
        "sentence": res[0]['sentence'],
        "phrase": res[0]['phrase'],
        "exam": res[0]['exam'],
        "belong": res[0]['belong'],
        "checkTimes": res[0]['checkTimes'],
        "errorTimes": res[0]['errorTimes'],
        "helpText": res[0]['helpText'],
        "soundText": res[0]['soundText'],
      };
    } else {
      return {};
    }
  }

  /// Words 表单查询搜索实例
  Future<List<Map>> getWordsBySearch(Map paramsForm) async {
    await initDB();
    String query = "SELECT * FROM $wordsTB";
    /// 设置统一搜索
    String searchStr = "";
    if (paramsForm['search'] != null && paramsForm['search'] != '') {
      searchStr = "( name LIKE '%${paramsForm['search']}%' or belong LIKE '%${paramsForm['search']}%' or checkTimes LIKE '%${paramsForm['search']}%' or errorTimes LIKE '%${paramsForm['search']}%' or helpText LIKE '%${paramsForm['search']}%' or soundText LIKE '%${paramsForm['search']}%' )";
    }
    /// 参数搜索类型
    Map paramsMap = {
      'id': 'equal',
      'name': 'like',
      'trans': 'like',
      'ukphone': 'none',
      'ukspeech': 'none',
      'usphone': 'none',
      'usspeech': 'none',
      'syno': 'none',
      'relWord': 'none',
      'sentence': 'none',
      'phrase': 'none',
      'exam': 'none',
      'belong': 'like',
      'checkTimes': 'equal',
      'errorTimes': 'equal',
      'helpText': 'like',
      'soundText': 'like',
    };
    String queryParams = "";
    paramsForm.forEach((key, value) {
      if (value != null && value != "") {
        String params = "";
        /// 时间参数需要自行按照如下样式进行调整
        if (key == "endTimeStart") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 00:00:00";
          params = " datetime(endTime) >= datetime('$newValue')  ";
        } else if (key == "endTimeEnd") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 23:59:59";
          params = " datetime(endTime) <= datetime('$newValue')  ";
        } else if (key == "startTimeStart") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 00:00:00";
          params = " datetime(startTime) >= datetime('$newValue')  ";
        } else if (key == "startTimeEnd") {
          String newValue = DateFormat('yyyy-MM-dd').format(DateTime.parse(value)) + " 23:59:59";
          params = " datetime(startTime) <= datetime('$newValue')  ";
        } else if (key != "id") {
          if ( paramsMap.containsKey(key) && paramsMap[key] == 'equal'){
            params = " $key = '$value' ";
          } else if ( paramsMap.containsKey(key) && paramsMap[key] == 'like' ) {
            params = " $key LIKE '%$value%' ";
          }
        } else if (key == "id") {
          params = " $key = '$value' ";
        }
        if ( params != "" ) {
          if ( queryParams != "" ) {
            queryParams = queryParams + " and " + params;
          } else {
            queryParams = queryParams + params;
          }
        }
      }
    });
    queryParams = (searchStr != "" ? (queryParams != "" ? searchStr + " and ": searchStr):"") + queryParams;
    if ( queryParams != "" ) { query = query + " WHERE " + queryParams; }
    /// 需要排序按需添加
    /// query = query + " ORDER BY id DESC , updateTime ASC";
    /// 按参数判断是否分页
    if (paramsForm.containsKey('pageSize') && paramsForm['pageSize'] != null && paramsForm['pageSize'] != '' && paramsForm['pageSize'] != 'null' && paramsForm.containsKey('pageNum') && paramsForm['pageNum'] != null && paramsForm['pageNum'] != '' && paramsForm['pageNum'] != 'null') {
      var pageSize = int.parse(paramsForm['pageSize'].toString());
      var pageNum = int.parse(paramsForm['pageNum'].toString());
      query = query + "  Limit $pageSize Offset ${pageSize * (pageNum - 1)}";
    }
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    return List.generate(
      res.length,
          (i) => {
        "id": res[i]['id'],
        "name": res[i]['name'],
        "trans": res[i]['trans'],
        "ukphone": res[i]['ukphone'],
        "ukspeech": res[i]['ukspeech'],
        "usphone": res[i]['usphone'],
        "usspeech": res[i]['usspeech'],
        "syno": res[i]['syno'],
        "relWord": res[i]['relWord'],
        "sentence": res[i]['sentence'],
        "phrase": res[i]['phrase'],
        "exam": res[i]['exam'],
        "belong": res[i]['belong'],
        "checkTimes": res[i]['checkTimes'],
        "errorTimes": res[i]['errorTimes'],
        "helpText": res[i]['helpText'],
        "soundText": res[i]['soundText'],
      },
    );
  }

  /// Words 表单按条件查询实例
  /// 在 getWordsBySearch(Map paramsForm) 中集成了所有的搜索参数，如无必要不需要单独使用此搜索方法
  Future<List<Map>> getWordsByName(String name) async {
    await initDB();
    String query = "SELECT * FROM $wordsTB WHERE name LIKE '%$name%'";
    /// 需要排序按需添加
    /// query = query + " ORDER BY id DESC , updateTime ASC";
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    return List.generate(
      res.length,
          (i) => {
        "id": res[i]['id'],
        "name": res[i]['name'],
        "trans": res[i]['trans'],
        "ukphone": res[i]['ukphone'],
        "ukspeech": res[i]['ukspeech'],
        "usphone": res[i]['usphone'],
        "usspeech": res[i]['usspeech'],
        "syno": res[i]['syno'],
        "relWord": res[i]['relWord'],
        "sentence": res[i]['sentence'],
        "phrase": res[i]['phrase'],
        "exam": res[i]['exam'],
        "belong": res[i]['belong'],
        "checkTimes": res[i]['checkTimes'],
        "errorTimes": res[i]['errorTimes'],
        "helpText": res[i]['helpText'],
        "soundText": res[i]['soundText'],
      },
    );
  }

  /// Words 表单按条件查询实例
  /// 在 getWordsBySearch(Map paramsForm) 中集成了所有的搜索参数，如无必要不需要单独使用此搜索方法
  Future<List<Map>> getWordsByBelong(String belong) async {
    await initDB();
    String query = "SELECT * FROM $wordsTB WHERE belong LIKE '%$belong%'";
    /// 需要排序按需添加
    /// query = query + " ORDER BY id DESC , updateTime ASC";
    List<Map<String, dynamic>> res = await db!.rawQuery(query);
    return List.generate(
      res.length,
          (i) => {
        "id": res[i]['id'],
        "name": res[i]['name'],
        "trans": res[i]['trans'],
        "ukphone": res[i]['ukphone'],
        "ukspeech": res[i]['ukspeech'],
        "usphone": res[i]['usphone'],
        "usspeech": res[i]['usspeech'],
        "syno": res[i]['syno'],
        "relWord": res[i]['relWord'],
        "sentence": res[i]['sentence'],
        "phrase": res[i]['phrase'],
        "exam": res[i]['exam'],
        "belong": res[i]['belong'],
        "checkTimes": res[i]['checkTimes'],
        "errorTimes": res[i]['errorTimes'],
        "helpText": res[i]['helpText'],
        "soundText": res[i]['soundText'],
      },
    );
  }

}

DBHelper localData = DBHelper._();
